/*
 * @Author: your name
 * @Date: 2021-09-15 09:34:48
 * @LastEditTime: 2021-09-16 13:45:08
 * @LastEditors: Please set LastEditors
 * @Description: In User Settings Edit
 * @FilePath: \media_system\routes\index.js
 */
const { Sequelize, DataTypes } = require("sequelize");
const { User, Food } = require('../database/index')
const Router = require('@koa/router')
const { formatResponse, resizeImg, filterColumn } = require("../utils/common");
const uploadLocal = require('../utils/uploadLocal');
const readXlsxFile = require('read-excel-file/node')
const { uploadQiniu, getFileUrl } = require('../utils/uploadQiNiu');
const Redis = require("ioredis");
const redis = new Redis();
const router = new Router()
// 配置get路由
// router.get('/createFood', async function (ctx, next) {
//   const currentUser = await User.findOne({ where: { username: 'zs' } })
//   const currentLivingRoom = await LivingRoom.create({
//     title: '直播间1',
//     type: 1,
//   })
//   await currentUser.addLivingRooms([currentLivingRoom])
//   ctx.body = (await currentUser.getLivingRooms())[0].title
// })
/**
 * @swagger
 * /food/add: #接口访问路径(拼接上之前swagger.js中的host)
 *   post: #请求方式
 *     summary: 添加食物信息  #接口信息
 *     description: 添加食物信息 #接口描述
 *     tags: [食物] #接口分组
 *     produces:
 *       - application/json
 *     requestBody:
 *         content:
 *            multipart/form-data:
 *               schema:
 *                  type: object
 *                  required:
 *                  - foodName
 *                  - type
 *                  - protein
 *                  - fat
 *                  - carbohydrate
 *                  - caloric
 *                  - avator
 *                  properties:
 *                     foodName:          
 *                        type: string
 *                        description: 食物名称
 *                     protein:          
 *                        type: number
 *                        description: 蛋白质(g)
 *                     fat:          
 *                        type: number
 *                        description: 脂肪(g)
 *                     carbohydrate:          
 *                        type: number
 *                        description: 碳水(g)
 *                     caloric:          
 *                        type: number
 *                        description: 热量(kcal)
 *                     fiber:          
 *                        type: number
 *                        description: 不溶性膳食纤维(g)
 *                     cholesterol:          
 *                        type: number
 *                        description: 胆固醇(mg)
 *                     vitamina:          
 *                        type: number
 *                        description: 维生素A(ugRAE)
 *                     vitaminc:          
 *                        type: number
 *                        description: 维生素C(mg)
 *                     calcium:          
 *                        type: number
 *                        description: 钙(mg)
 *                     potassium:          
 *                        type: number
 *                        description: 钾(mg)
 *                     sodium:          
 *                        type: number
 *                        description: 钠(mg)
 *                     givalue:          
 *                        type: string
 *                        description: 食物血糖生成指数
 *                     type:          
 *                        type: integer
 *                        enum: ["0","1", "2","3","4", "5","6","7", "8","9","10", "11","12","13", "14","15","16", "17","18","19", "20","21"]
 *                        description: 食物类型（0其他 1谷类及制品 2薯类、淀粉及制品 3干豆类及制品 4蔬菜类及制品 5菌藻类 6水果类及制品 7坚果、种子类 8植物油 9畜肉类及制品 10禽肉类及制品 11乳类及制品 12蛋类及制品 13鱼虾蟹贝类 14油脂 15婴幼儿食品 16小吃、甜饼 17速食食品 18饮料类 19含酒精饮料 20糖、果脯和蜜饯、蜂蜜类 21调味品类）
 *                     unit:          
 *                        type: integer
 *                        enum: [ "0", "1"]
 *                        description: 食物状态（0（固体） 1（液体））
 *                     avator:          
 *                        type: string
 *                        format: binary
 *                        description: 食物封面照片
 *                     desc:       
 *                        type: string
 *                        description: 食物描述
 *     responses: #返回结果
 *       200:
 *         description: 成功
 */
router.post("/add", async function (ctx, next) {
  let err = await uploadLocal.single('avator')(ctx, next).then(res => res).catch(err => err)
  if (err) {
    ctx.body = formatResponse("500", {
      code: 0,
      message: err.message
    }, "fail");
  } else {
    let foodInfo = ctx.request.body;
    //完成图片logo的缩放和转换成base64
    const data64str = await resizeImg(ctx.file)
    //完成七牛云上传
    try {
      const result = await uploadQiniu(ctx.file).catch((error) => {
        throw error
      })
      foodInfo.fiber = foodInfo.fiber || 0
      foodInfo.cholesterol = foodInfo.cholesterol || 0
      foodInfo.vitamina = foodInfo.vitamina || 0
      foodInfo.vitaminc = foodInfo.vitaminc || 0
      foodInfo.calcium = foodInfo.calcium || 0
      foodInfo.potassium = foodInfo.potassium || 0
      foodInfo.sodium = foodInfo.sodium || 0
      foodInfo = { ...foodInfo, logoImg: data64str, avator: getFileUrl(result.respBody.key) }
      const currentFood = await Food.create({
        ...foodInfo,
      });
      if (currentFood) {
        //更新redis set
        if (currentFood.unit == null || currentFood.unit == '' || currentFood.unit == " ") {
          currentFood.unit = 0
        }
        await redis.sadd('foodInfos', currentFood.foodName + "#" + currentFood.foodId + "_" + currentFood.unit)
        ctx.body = formatResponse("200", { ...currentFood.get({ plain: true }) }, "success");
      } else {
        ctx.body = formatResponse("500", { message: '数据库存储失败' }, "fail");
      }
    } catch (error) {
      ctx.body = formatResponse("500", error, "fail");
    }
  }

});
/**
 * @swagger
 * /food/getAllFood:
 *   get:
 *     security:
 *       - bearerAuth: []
 *     summary: 查询所有食物
 *     description: 查询所有食物
 *     tags: [食物]
 *     responses:
 *       200:
 *         description: 成功
 */
router.get("/getAllFood", async function (ctx, next) {
  let foods = await Food.findAll();
  if (foods) {
    ctx.body = formatResponse("200", foods, "success");
  } else {
    ctx.body = formatResponse("500", "", "fail");
  }
});
/**
 * @swagger
 * /food/getAllFoodByName:
 *   get:
 *     security:
 *       - bearerAuth: []
 *     summary: 根据名称查询所有食物
 *     description: 根据名称查询所有食物
 *     tags: [食物]
 *     parameters:
 *       - name: foodName
 *         in: query #参数存放位置。query是存放到请求头里边
 *         required: true
 *         description: 食物名称
 *         type: string
 *     responses:
 *       200:
 *         description: 成功
 */
router.get("/getAllFoodByName", async function (ctx, next) {
  const Op = Sequelize.Op;
  const { foodName } = ctx.request.query;
  let foods = await Food.findAll({
    where: { foodName: { [Op.like]: `%${foodName}%` } }
  });
  if (foods) {
    ctx.body = formatResponse("200", foods, "success");
  } else {
    ctx.body = formatResponse("500", "", "fail");
  }
});

/**
 * @swagger
 * /food/getAllFoodByNameFR:
 *   get:
 *     security:
 *       - bearerAuth: []
 *     summary: 根据名称查询所有食物Redis版
 *     description: 根据名称查询所有食物Redis版
 *     tags: [食物]
 *     parameters:
 *       - name: foodName
 *         in: query #参数存放位置。query是存放到请求头里边
 *         required: true
 *         description: 食物名称
 *         type: string
 *     responses:
 *       200:
 *         description: 成功
 */
router.get("/getAllFoodByNameFR", async function (ctx, next) {
  const Op = Sequelize.Op;
  const { foodName } = ctx.request.query;


  // const len = await redis.call('hlen', 'foodInfos')
  // const result = await redis.call('hscan','foodInfos', 0, 'match', `*${foodName}*_*`,'count',len)

  // sscan foodInfos 0 match *肉*_* count 400
  const len = await redis.call('scard', 'foodInfos')
  const result = await redis.call('sscan', 'foodInfos', 0, 'match', `*${foodName}*#*`, 'count', len)
  if (result) {
    ctx.body = formatResponse("200", result, "success");
  } else {
    ctx.body = formatResponse("500", "", "fail");
  }
});
/**
 * @swagger
 * /food/getFoodsByPageAndCondition:
 *   get:
 *     security:
 *       - bearerAuth: []
 *     summary: 条件分页查询食物
 *     description: 条件分页查询食物
 *     tags: [食物]
 *     parameters:
 *       - name: foodName
 *         in: query #参数存放位置。query是存放到请求头里边
 *         description: 食物名称
 *         type: string
 *       - name: type
 *         in: query #参数存放位置。query是存放到请求头里边
 *         description: 食物类型（0肉类 1蔬菜 2水果 3其他）
 *         type: integer
 *       - name: protein
 *         in: query #参数存放位置。query是存放到请求头里边
 *         description: 蛋白质数值范围
 *         schema:
 *          type: array
 *          items:
 *            type: number
 *       - name: fat
 *         in: query #参数存放位置。query是存放到请求头里边
 *         description: 脂肪数值范围
 *         schema:
 *          type: array
 *          items:
 *            type: number
 *       - name: carbohydrate
 *         in: query #参数存放位置。query是存放到请求头里边
 *         description: 碳水数值范围
 *         schema:
 *          type: array
 *          items:
 *            type: number
 *       - name: caloric
 *         in: query #参数存放位置。query是存放到请求头里边
 *         description: 热量数值范围
 *         schema:
 *          type: array
 *          items:
 *            type: number
 *       - name: currentPage
 *         in: query #参数存放位置。query是存放到请求头里边
 *         required: true
 *         description: 当前第几页
 *         type: string
 *       - name: pageSize
 *         in: query #参数存放位置。query是存放到请求头里边
 *         required: true
 *         description: 一页几条记录
 *         type: string
 *     responses:
 *       200:
 *         description: 成功
 */
router.get("/getFoodsByPageAndCondition", async function (ctx, next) {
  const Op = Sequelize.Op;
  const { currentPage, pageSize, foodName, type, protein, fat, carbohydrate, caloric } = ctx.request.query;
  offset = (currentPage - 1) * pageSize || 0;
  //条件1：食物名称
  //条件2：蛋白质(从多少到多少)
  //条件3：脂肪(从多少到多少)
  //条件4：碳水(从多少到多少)
  //条件5：热量(从多少到多少)
  //条件6：食物类型(0肉类 1蔬菜 2水果 3其他)
  const condition1 = foodName ? { foodName: { [Op.like]: `%${foodName}%` } } : null;
  const condition2 = protein ? protein.length > 0 ? { 'protein': { [Op.between]: protein } } : null : null;
  const condition3 = fat ? fat.length > 0 ? { 'fat': { [Op.between]: fat } } : null : null;
  const condition4 = carbohydrate ? carbohydrate.length > 0 ? { 'carbohydrate': { [Op.between]: carbohydrate } } : null : null;
  const condition5 = caloric ? caloric.length > 0 ? { 'caloric': { [Op.between]: caloric } } : null : null;
  const condition6 = type ? { type: { [Op.eq]: type } } : null;
  let result = await Food.findAndCountAll({
    where: Object.assign({}, condition1, condition2, condition3, condition4, condition5, condition6),
    order: [['createdAt', 'DESC']],
    offset,
    limit: parseInt(pageSize),
  });
  if (result) {
    ctx.body = formatResponse("200", result, "success");
  } else {
    ctx.body = formatResponse("500", "", "fail");
  }
});
/**
 * @swagger
 * /food/deleteFood:
 *   get:
 *     security:
 *       - bearerAuth: []
 *     summary: 根据食物ID删除食物
 *     description: 根据食物ID删除食物
 *     tags: [食物]
 *     parameters:
 *       - name: foodIds
 *         in: query #参数存放位置。query是存放到请求头里边
 *         required: true
 *         description: 食物ID
 *         schema:
 *          type: array
 *          items:
 *            type: string
 *     responses:
 *       200:
 *         description: 成功
 */
router.get("/deleteFood", async function (ctx, next) {
  const { foodIds } = ctx.request.query;
  const count = await Food.destroy({
    where: {
      foodId: foodIds
    },
    individualHooks: true
  });
  //删除redis set中的数据 待定

  if (count > 0) {
    ctx.body = formatResponse("200", "", "success");
  } else {
    ctx.body = formatResponse("500", "", "fail");
  }
});

/**
 * @swagger
 * /food/updateFood: #接口访问路径(拼接上之前swagger.js中的host)
 *   post: #请求方式
 *     security:
 *       - bearerAuth: []
 *     summary: 根据食物ID更新食物信息  #接口信息
 *     description: 根据食物ID更新食物信息 #接口描述
 *     tags: [食物] #接口分组
 *     produces:
 *       - application/json
 *     consumes:
 *       - application/json
 *     requestBody:
 *         content:
 *            multipart/form-data:
 *               schema:
 *                  type: object
 *                  properties:
 *                     foodId:          # <!--- form field name
 *                        type: string
 *                        description: 食物ID
 *                     foodName:          
 *                        type: string
 *                        description: 食物名称
 *                     type:          
 *                        type: integer
 *                        enum: ["0","1", "2","3","4", "5","6","7", "8","9","10", "11","12","13", "14","15","16", "17","18","19", "20","21"]
 *                        description: 食物类型（0 其他 1谷类及制品 2薯类、淀粉及制品 3干豆类及制品 4蔬菜类及制品 5菌藻类 6水果类及制品 7坚果、种子类 8植物油 9畜肉类及制品 10禽肉类及制品 11乳类及制品 12蛋类及制品 13鱼虾蟹贝类 14油脂 15婴幼儿食品 16小吃、甜饼 17速食食品 18饮料类 19含酒精饮料 20糖、果脯和蜜饯、蜂蜜类 21调味品类）
 *                     protein:          
 *                        type: number
 *                        description: 蛋白质(g)
 *                     fat:          
 *                        type: number
 *                        description: 脂肪(g)
 *                     carbohydrate:          
 *                        type: number
 *                        description: 碳水(g)
 *                     caloric:          
 *                        type: number
 *                        description: 热量(kcal)
 *                     fiber:          
 *                        type: number
 *                        description: 不溶性膳食纤维(g)
 *                     cholesterol:          
 *                        type: number
 *                        description: 胆固醇(mg)
 *                     vitamina:          
 *                        type: number
 *                        description: 维生素A(ugRAE)
 *                     vitaminc:          
 *                        type: number
 *                        description: 维生素C(mg)
 *                     calcium:          
 *                        type: number
 *                        description: 钙(mg)
 *                     potassium:          
 *                        type: number
 *                        description: 钾(mg)
 *                     sodium:          
 *                        type: number
 *                        description: 钠(mg)
 *                     givalue:          
 *                        type: string
 *                        description: 食物血糖生成指数
 *                     unit:          
 *                        type: integer
 *                        enum: [ "0", "1"]
 *                        description: 食物状态（0（固体） 1（液体)）
 *                     avator:          
 *                        type: string
 *                        format: binary
 *                        description: 食物封面照片
 *                     desc:       
 *                        type: string
 *                        description: 食物描述
 *     responses: #返回结果
 *       200:
 *         description: 成功
 */
router.post("/updateFood", async function (ctx, next) {
  let err = await uploadLocal.single('avator')(ctx, next).then(res => res).catch(err => err)
  if (err) {
    ctx.body = formatResponse("500", {
      code: 0,
      message: '上传图片故障' + err.message
    }, "fail");
  } else {
    try {
      let foodInfo = ctx.request.body;
      foodInfo = filterColumn(foodInfo)
      if (ctx.file) {
        //完成图片logo的缩放和转换成base64
        const data64str = await resizeImg(ctx.file)
        //完成七牛云上传
        let result = await uploadQiniu(ctx.file).catch((error) => {
          throw error
        })
        foodInfo = { ...foodInfo, logoImg: data64str, avator: getFileUrl(result.respBody.key) }
      }
      // console.log(1111111111111,foodInfo)
      let result = await Food.update(
        {
          ...foodInfo
        },
        {
          where: {
            foodId: foodInfo.foodId,
          },
          individualHooks: true
        }
      );
      //更新redis set中的数据 待定

      if (result[0] > 0) {
        ctx.body = formatResponse("200", { code: 200, message: '食物更新成功' }, "success");
      } else {
        ctx.body = formatResponse("500", { code: 500, message: '食物更新失败' }, "fail");
      }
    } catch (error) {
      ctx.body = formatResponse("500", error, "fail");
    }
  }
});

/**
 * @swagger
 * /food/excelImportFoods: #接口访问路径(拼接上之前swagger.js中的host)
 *   post: #请求方式
 *     security:
 *       - bearerAuth: []
 *     summary: excel批量导入foods  #接口信息
 *     description: excel批量导入foods #接口描述
 *     tags: [食物] #接口分组
 *     requestBody:
 *         content:
 *            multipart/form-data:
 *               schema:
 *                  type: object
 *                  properties:
 *                     avator:          
 *                        type: string
 *                        format: binary
 *                        description: 食物默认封面
 *                     excelfile:          
 *                        type: string
 *                        format: binary
 *                        description: excel文件
 *     responses: #返回结果
 *       200:
 *         description: 成功
 */
router.post("/excelImportFoods", async function (ctx, next) {
  const err = await uploadLocal.fields([
    { name: 'avator', maxCount: 1 },
    { name: 'excelfile', maxCount: 1 }
  ])(ctx, next).then(res => res).catch(err => err)
  if (err) {
    // console.log(err)
    ctx.body = formatResponse("500", {
      code: 0,
      message: err.message
    }, "fail");
  } else {
    //完成图片logo的缩放和转换成base64
    const data64str = await resizeImg(ctx.files['avator'][0])
    //完成七牛云上传
    try {
      const result = await uploadQiniu(ctx.files['avator'][0]).catch((error) => {
        throw error
      })
      const schema = {
        '食物名称': {
          prop: 'foodName',
          type: String
        },
        '食物类型': {
          prop: 'type',
          type: Number
        },
        '蛋白质(g)': {
          prop: 'protein',
          type: Number
        },
        '脂肪(g)': {
          prop: 'fat',
          type: Number
        },
        '碳水(g)': {
          prop: 'carbohydrate',
          type: Number
        },
        '热量(kcal)': {
          prop: 'caloric',
          type: Number
        },
        '不溶性膳食纤维(g)': {
          prop: 'fiber',
          type: Number
        },
        '胆固醇(mg)': {
          prop: 'cholesterol',
          type: Number
        },
        '维生素A(ugRAE)': {
          prop: 'vitamina',
          type: Number
        },
        '维生素C(mg)': {
          prop: 'vitaminc',
          type: Number
        },
        '钙(mg)': {
          prop: 'calcium',
          type: Number
        },
        '钾(mg)': {
          prop: 'potassium',
          type: Number
        },
        '钠(mg)': {
          prop: 'sodium',
          type: Number
        },
        '血糖生成指数（GI）值': {
          prop: 'givalue',
          type: String
        },
        '食物状态(固态/液态)': {
          prop: 'unit',
          type: Number
        },
        '食物描述': {
          prop: 'desc',
          type: String
        },
      }
      const picUrl = getFileUrl(result.respBody.key)
      //完成excel保存
      const length = await readXlsxFile(ctx.files['excelfile'][0].path, { schema }).then(async ({ rows, errors }) => {
        // console.log(77777,rows,errors)
        rows = rows.map((r) => {
          r.protein = r.protein || 0
          r.fat = r.fat || 0
          r.carbohydrate = r.carbohydrate || 0
          r.caloric = r.caloric || 0
          r.fiber = r.fiber || 0
          r.cholesterol = r.cholesterol || 0
          r.vitamina = r.vitamina || 0
          r.vitaminc = r.vitaminc || 0
          r.calcium = r.calcium || 0
          r.potassium = r.potassium || 0
          r.sodium = r.sodium || 0
          return {
            ...r,
            logoImg: data64str,
            avator: picUrl
          }
        })
        if (errors.length === 0) {
          const foods = await Food.bulkCreate(rows);
          if (foods.length > 0) {
            //导入成功后放入redis
            for (let i = 0; i < foods.length; i++) {
              if (foods[i].unit == null || foods[i].unit == '' || foods[i].unit == " ") {
                foods[i].unit = 0
              }
              await redis.sadd('foodInfos', foods[i].foodName + "#" + foods[i].foodId + "_" + foods[i].unit)
            }
          }
          return foods.length;
        } else {
          throw errors
        }
      })
      if (length > 0) {
        ctx.body = formatResponse("200", { code: 200, message: '数据导入成功' }, "success");
      } else {
        ctx.body = formatResponse("500", { code: 500, message: '数据导入失败' }, "fail");
      }
    } catch (error) {
      ctx.body = formatResponse("500", { code: 500, error }, "fail");
    }
  }
});

/**
 * @swagger
 * /food/getFoodDetailById:
 *   get:
 *     security:
 *       - bearerAuth: []
 *     summary: 根据食物ID查询食物详情
 *     description: 根据食物ID查询食物详情
 *     tags: [食物]
 *     parameters:
 *       - name: foodId
 *         in: query #参数存放位置。query是存放到请求头里边
 *         description: 食物ID
 *         type: string
 *     responses:
 *       200:
 *         description: 成功
 */
router.get("/getFoodDetailById", async function (ctx, next) {
  const { foodId } = ctx.request.query;
  let food = await Food.findOne({
    where: { foodId }
  });
  if (food) {
    ctx.body = formatResponse("200", food, "success");
  } else {
    ctx.body = formatResponse("500", "", "fail");
  }
});
module.exports = router